from fastapi import APIRouter
from fastapi.responses import JSONResponse
import pandas as pd

index_data = APIRouter()

df1 = pd.read_excel("C:/Users\Coco\Desktop\实习项目\data\附件1.xlsx")
# df2 = pd.read_excel("/实习项目/data/附件2.xlsx")
df3 = pd.read_excel("C:/Users\Coco\Desktop\实习项目\data\附件3.xlsx")

df3['日期'] = pd.to_datetime(df3['日期'])
df3['月份'] = df3['日期'].dt.month

df = pd.merge(df3, df1[["单品编码", "单品名称"]], on="单品编码")
# print(df.head(5))

grouped = df.groupby('单品名称')
# print(grouped)

result = {}
for name, group in grouped:
    # print(name)
    # print(group)
    unique_months = group['月份'].unique()
    total_months = len(unique_months)
    season = []
    season_list = [0] * 4
    if 3 in unique_months or 4 in unique_months or 5 in unique_months:
        season.append(" 春季")
        season_list[0] = 1
    if 6 in unique_months or 7 in unique_months or 8 in unique_months:
        season.append(" 夏季")
        season_list[1] = 1
    if 9 in unique_months or 10 in unique_months or 11 in unique_months:
        season.append(" 秋季")
        season_list[2] = 1
    if 12 in unique_months or 1 in unique_months or 2 in unique_months:
        season.append(" 冬季")
        season_list[3] = 1
    result[name] = {
        '出现的月份': unique_months,
        '总共出现的月份数': total_months,
        '出现的季节': season,
        "季节数": len(season),
        "季节列表": season_list
    }

count_all = 0
count_all_list = []
for key, value in result.items():
    if value['季节数'] == 4:
        count_all += 1
        count_all_list.append(key)
# print(f" 单品编码 {key} 出现在以下月份: {', '.join(map(str, value['出现的月份']))}，总出现的月份数: {value['总共出现的月份数']}, 出现在 {value['出现的季节']}")

# print(count_all)

# print(count_all_list)

############ 常年可供应的蔬菜和水果 时令蔬菜和水果 统计 #############
df['年份'] = df['日期'].dt.year
result = df.groupby(['单品编码', '年份']).agg({'日期': 'nunique'}).reset_index()
result.rename(columns={'日期': '天数'}, inplace=True)

#print(result)
max_days = result.groupby('单品编码')['天数'].max().reset_index()
# print(max_days)

filtered_df = max_days[max_days['天数'] <= 15]
cnt = 0
cnt_list = []
for index, row in filtered_df.iterrows():
    cnt_list.append(row['单品编码'])
    # print(f" 单品编码：{row['单品编码']}，一年最多出现{row['天数']}天")
    cnt += 1
# print(cnt)


data_pie = [{'name': '常年性', 'value': count_all},
            {'name': '时令性', 'value': cnt},
            {'name': '季节性', 'value': 251 - count_all - cnt}]


@index_data.get("/get_index_data")
async def get_index_data():
    result = data_pie
    return JSONResponse(result)
